﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace MedicalManagementAPP
{
    static class Connection
    {
        static readonly String connString = "Data Source = .\\SQL2005;Initial Catalog = MedicalManagementSystem;Integrated Security=true";
        /// <summary>
        /// 获取SqlDataAdapter
        /// </summary>
        /// <param name="sql">需要的SQL语句</param>
        /// <param name="parameters">需要的参数数组</param>
        /// <returns>对应的SqlDataAdapter</returns>
        private static SqlDataAdapter GetSqlQueryAdapter(string sql, SqlParameter[] parameters)
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = connString;
            conn.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = sql;
            cmd.Parameters.AddRange(parameters);
            SqlDataAdapter dataAdapter = new SqlDataAdapter();
            dataAdapter.SelectCommand = cmd;
            return dataAdapter;
        }
        /// <summary>
        /// 释放SqlDataAdapter占用的资源
        /// </summary>
        /// <param name="adapter">需要被释放的SqlDataAdapter</param>
        private static void DisposeAdapter(SqlDataAdapter adapter)
        {
            adapter.SelectCommand.Parameters.Clear();
            adapter.SelectCommand.Dispose();
            adapter.SelectCommand.Connection.Close();
        }
        /// <summary>
        /// 执行一条不需要返回数据的SQL语句
        /// </summary>
        /// <param name="sql">需要执行的语句</param>
        /// <param name="parameters">语句的参数</param>
        /// <returns>true代表执行成功，否则失败</returns>
        public static bool SqlNoQuery(string sql, SqlParameter[] parameters)
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = connString;
            conn.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = sql;
            cmd.Parameters.AddRange(parameters);
            try
            {
                cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception)
            {
                return false;
            }
            finally
            {
                cmd.Parameters.Clear();
                cmd.Dispose();
                conn.Close();
            }
        }
        /// <summary>
        /// 执行一条需要返回DataTable的SQL语句（带参数）
        /// </summary>
        /// <param name="sql">需要执行的语句</param>
        /// <param name="parameters">该语句的参数</param>
        /// <returns>查询出的数据表</returns>
        public static DataTable SqlQuery(string sql, SqlParameter[] parameters)
        {
            SqlDataAdapter dataAdapter = Connection.GetSqlQueryAdapter(sql, parameters);
            try
            {
                DataSet dataSet = new DataSet();
                dataAdapter.Fill(dataSet, "Info");
                return dataSet.Tables["Info"];
            }
            catch (Exception e)
            {
                Console.Out.WriteLine(e);
                return null;
            }
            finally
            {
                DisposeAdapter(dataAdapter);
            }

        }
        /// <summary>
        /// 执行一条需要返回DataTable的SQL语句
        /// </summary>
        /// <param name="sql">需要执行的语句</param>
        /// <returns>查询出的数据表</returns>
        public static DataTable SqlQuery(string sql)
        {
            return Connection.SqlQuery(sql, new SqlParameter[0]);
        }

        public static bool UpdateData(string sql, SqlParameter[] parameters, DataTable dataTable)
        {
            SqlDataAdapter adapter = GetSqlQueryAdapter(sql, parameters);
            _ = new SqlCommandBuilder(adapter);
            try
            {
                adapter.Update(dataTable);
                return true;
            }
            catch (Exception)
            {
                return false;
            }
            finally
            {
                adapter.SelectCommand.Connection.Close();
                adapter.Dispose();
            }
        }
    }
}
